#import <Foundation/Foundation.h>
#import <objc/runtime.h>
#import <sqlite3.h>
#import <UIKit/UIApplication.h>
#import <LockInfo/Plugin.h>
#import <CoreLocation/CoreLocation.h>

#import "framework/FWCommon.h"
#import "framework/FWApplication.h"
#import "framework/FWDBAccess.h"
#import "framework/FWPreferences.h"
#import "framework/FWPlugin.h"

#import "PICommon.h"
#import "PIIntegration.h"
#import "PIPlugin.h"
#import "PIContextObject.h"
#import "PITaskPlugin.h"
#import "PITaskStatus.h"
#import "PIContext.h"
#import "PIAlarm.h"

#import "PITaskIntegration.h"
#import "PITaskIntegration202.h"
#import "PITaskIntegration250.h"
#import "PITask.h"


static NSInteger const kSQLBasicTaskList = 1;
static NSInteger const kSQLAugmentedTask = 3;
static NSInteger const kSQLArrayOfContexts = 4;
static NSInteger const kSQLMaxTaskID = 5;
static NSInteger const kSQLNotes = 6;
static NSInteger const kSQLLocationContexts = 7;
static NSInteger const kSQLAlarms = 8;

static NSInteger const kMaxSearchItems = 50;

NSString* const kFieldNameContextStatus = @"xxx_contextstat";
NSString* const kFieldNameChildCount = @"xxx_children";
NSString* const kFieldNameImportance = @"xxx_imp";
NSString* const kFieldNameStartDate = @"xxx_start";
NSString* const kFieldNameDueDate = @"xxx_due";
NSString* const kFieldNameTagID = @"xxx_tagID";


NSString* const kCompletePasteboard = @"net.webis.pocketinformant.tasksCompleted";


@implementation PITaskIntegration


+ (PITaskIntegration*)implementationForApp:(FWApplication*)app
{
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;

    if (plugin.dbVersion == PIDatabaseVersion202)
        return [[[PITaskIntegration202 alloc] init] autorelease];
    if (plugin.dbVersion == PIDatabaseVersion250)
        return [[[PITaskIntegration250 alloc] init] autorelease];
    
    return nil;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Fetching a list of Tasks

- (void)addSortField:(NSMutableString*)current field:(int)field ascending:(BOOL)ascending invert:(BOOL)invert prefs:(FWPreferences*)prefs
{
    DebugLog(@"LI:PITask: Adding PI2 SQL sort field, current=%@; field=%i; ascend=%i; invert=%i", current, field, ascending, invert);        
    
    if (field == SortFieldOptionsNone)
        return;
    
    NSMutableString* fieldName;
    BOOL atStart;
    switch (field) {
        case SortFieldOptionsImportance:
            DebugLog(@"LI:PITask: Adding importance sort field");
            fieldName = [NSMutableString stringWithCapacity:80];
            if (invert)
                [fieldName appendFormat:@"case %@ when %d then 99 else %@ end",
                 kFieldNameImportance, PIImportanceNone, kFieldNameImportance];
            else
                [fieldName appendString:kFieldNameImportance];
            
            [fieldName appendFormat:@"/*Importance[%i]*/", invert];
            break;
            
        case SortFieldOptionsPriority:
            // Sort by initial letter if present or '-/~' if starts with a number - so numbers precede or follow letters
            // depending upon the invert flag state. If no value at all then use '~~' to ensure no fc value is always
            // at the end of the list.
            DebugLog(@"LI:PITask: Adding priority sort field");
            fieldName = [NSMutableString stringWithCapacity:240];
            [fieldName appendFormat:
             @"case when ifnull(T.ZFCPRIORITY,'')='' then '~~'"
             " when abs(substr(T.ZFCPRIORITY,1,1))>0 then '%@'"
             " else substr(T.ZFCPRIORITY,1,1) end", invert ? @"~" : @"-"];
            if (!ascending)
                [fieldName appendString:@" DESC"];
            
            [fieldName appendString:
             @", case when abs(substr(T.ZFCPRIORITY,1,1))=0 then abs(substr(T.ZFCPRIORITY,2)) else abs(T.ZFCPRIORITY) end"];
            
            [fieldName appendFormat:@"/*Priority[%i]*/", invert];
            break;
            
        case SortFieldOptionsAction:
            DebugLog(@"LI:PITask: Adding action sort field");
            fieldName = [NSMutableString stringWithCapacity:80];
            if (invert)
                [fieldName setString:@"case T.ZACTIONINTEGER when 0 then 99 else T.ZACTIONINTEGER end"];
            else
                [fieldName setString:@"T.ZACTIONINTEGER"];
            
            [fieldName appendFormat:@"/*Action[%i]*/", invert];
            break;
            
        case SortFieldOptionsDueDate:
            // PI stores end times as local rather than UTC so we need to convert the local time to UTC
            // in order to compare with an end date. This will be a hack as srictly we need the number of
            // seconds offset on the given day to account for DST. That's the way that the display functions
            // work.
            DebugLog(@"LI:PITask: Adding due date sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            if (invert)
                [fieldName appendFormat:@"ifnull(%@,9999999999)", kFieldNameDueDate];
            else
                [fieldName appendString:kFieldNameDueDate];
            [fieldName appendFormat:@"/*Due[%i]*/", invert];
            break;
            
        case SortFieldOptionsStartDate:
            DebugLog(@"LI:PITask: Adding start date sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            if (invert)
                [fieldName appendFormat:@"ifnull(%@,9999999999)", kFieldNameStartDate];
            else
                [fieldName appendString:kFieldNameStartDate];
            [fieldName appendFormat:@"/*Start[%i]*/", invert];
            break;
            
        case SortFieldOptionsTitle:
            DebugLog(@"LI:PITask: Adding title sort field");
            fieldName = [NSMutableString stringWithCapacity:40];
            [fieldName appendFormat:@"T.ZTITLE/*Title[%i]*/", invert];
            break;
            
        case SortFieldOptionsReorder:
            DebugLog(@"LI:PITask: Adding reorder sort field");
            fieldName = [NSMutableString stringWithCapacity:40];
            [fieldName appendFormat:@"T.ZSEQUENCE/*Reorder[%i]*/", invert];
            break;
            
        case SortFieldOptionsContext:
            DebugLog(@"LI:PITask: Adding context sort field");
            fieldName = [NSMutableString stringWithCapacity:50];
            [fieldName appendFormat:@"%@/*Context[%i]*/", kFieldNameContextStatus, invert];
            break;
            
        case SortFieldOptionsContextFilter:
            // This sort is a little tricky. The idea is that if a set of context states may
            // be included in the task list then it should be added to the beginning of the
            // sort list. This prevents, say, high priority out of context items blocking low
            // priority in-context items.
            DebugLog(@"LI:PITask: Adding context sort field");
            fieldName = [NSMutableString stringWithCapacity:100];
            atStart = [prefs boolValue:kPIOptionsShowContextAtStart withDefault:kPIOptionsShowContextAtStartDefault];
            
            // In-context items should always go at the top of the list
            [fieldName appendFormat:@"case when %@=%i then 0", kFieldNameContextStatus, ContextClassificationInContext];
            
            // No context items should be at the top of the list if they aren't always filtered
            [fieldName appendFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationNoContext,
             ([prefs intValue:kPIOptionsHideNoContext withDefault:kPIOptionsHideNoContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            // Out of context items should be at the top of the list if they aren't always filtered
            [fieldName appendFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationOutOfContext,
             ([prefs intValue:kPIOptionsHideOutOfContext withDefault:kPIOptionsHideOutOfContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            [fieldName appendFormat:@" else 2 end/*ContextFilter[%i]*/", invert];
            break;
            
        default:
            fieldName = nil;
            break;
    }
    
    if (fieldName) {
        if (!ascending)
            [fieldName appendString:@" DESC"];
        [current appendString:fieldName withSeparator:@", "];
    }
}

+ (NSMutableArray*)basicTaskList:(FWApplication*)app database:(FWDatabase*)database prefs:(FWPreferences*)prefs key:(NSString*)key parentKey:(NSString*)parentKey search:(NSString*)search
{
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    PITaskStatus* status = (PITaskStatus*)app.info;
    BOOL debug = plugin.isDebugMode;

    // If PI not found then do nothing
    if (!plugin.isPocketInformantPresent)
        return [NSMutableArray array];

    // Build array of contexts we are using
    NSMutableArray* contexts = [NSMutableArray arrayWithCapacity:20];
    if (status.isAutoContextOn) {
        if (NSArray* adds = (NSArray*)[app getAttribute:kPIApplicationContextLocation])
            [contexts addObjectsFromArray:adds];
        
        if (contexts.count == 0) {
            if (NSArray* adds = (NSArray*)[app getAttribute:kPIApplicationContextDefault])
                [contexts addObjectsFromArray:adds];
        }
    }
    if (status.isFixedContextOn) {
        if (NSArray* adds = (NSArray*)[app getAttribute:kPIApplicationContextFixed])
            [contexts addObjectsFromArray:adds];
    }

    NSMutableString* contextList = [NSMutableString stringWithCapacity:contexts.count * 3];
    [contextList appendString:@"-1"];
    for (PIContext* context in contexts) {
        [contextList appendFormat:@",%i", context.key];
    }
    

    PITaskIntegration* exec = [PITaskIntegration implementationForApp:app];

    NSMutableString* sql = [exec basicTaskListSQL:app prefs:prefs contexts:contextList key:key parentKey:parentKey search:search];

    // Don't return calendars in the ignore list
    if (NSArray* calendars = [prefs arrayValue:kPIOptionsHiddenPIEventCalendars withDefault:nil]) {
        NSMutableString* ignoreCalendars = [NSMutableString stringWithCapacity:calendars.count * 3];
        for (NSString* calendar in calendars) {
            [ignoreCalendars appendString:calendar withSeparator:@","];
        }
        if (ignoreCalendars.length > 0)
            [sql appendFormat:@" and T.ZCALENDAR not in (%@)/*IgnoreCals*/", ignoreCalendars];
    }
    
    
    // Only generate filter terms if the option to show all tasks is off    
    if (key) {
        // Only want a single key
        [sql appendFormat:@" and T.Z_PK=%@/*KeyOnly*/", key];
    }
    else if (parentKey) {
        // Only want children of a single key
        [sql appendFormat:@" and T.ZPARENTTASK=%@/*ChildrenOnly*/", parentKey];
    }
    else if (search) {
        // Only want tasks that match a pattern   
        search = [search stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        search = [search stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        [sql appendFormat:@" and T.ZTITLE like '%%%@%%'/*Search*/", search];
    }
    else if (![prefs boolValue:kPIOptionsShowAllTasks withDefault:kPIOptionsShowAllTasksDefault]) {
        NSString* inbox = @"1=1";
        int taskFiling = [prefs intValue:kPIOptionsShowFiling withDefault:kPIOptionsShowFilingDefault];
        if (taskFiling == TaskFilingInboxOnly)
            // Only tasks without tags or a context or a project or an action
            [sql appendFormat:@" and %@/*ShowInboxOnly*/", inbox];
        else if (taskFiling == TaskFilingFiledOnly)
            // Don't allow tasks without tags or a context or a project or an action
            [sql appendFormat:@" and not(%@)/*ShowFiledOnly*/", inbox];
        
        if ([prefs boolValue:kPIOptionsHideDeferred withDefault:kPIOptionsHideDeferredDefault])
            // Only allow tasks before Delegated
            [sql appendFormat:@" and T.ZACTIONINTEGER<%d/*HideDeferred*/", PIActionDelegated];
        
        // Get the date maxDays in the future
        NSTimeInterval maxDays = (NSTimeInterval)[prefs intValue:kPIOptionsDayLimit withDefault:kPIOptionsDayLimitDefault];
        NSTimeInterval seconds = maxDays * SECONDS_PER_DAY;
        
        // Get time of midnight today (ie past) in the UTC by building a new date from only the year, month
        // and day components of the current date. PI start dates are always UTC
        //NSDate *midnight = FWUTCForLocalDate(FWLocalDateForLocalMidnight());
        NSDate *midnight = FWLocalDateForLocalMidnight();
        if (debug)
            XLog(@"LI:PITask: Local midnight utc date = %@", midnight);
        
        if ([prefs boolValue:kPIOptionsHideFuture withDefault:kPIOptionsHideFutureDefault]) {
            // Only allow tasks starting before or at today's midnight or that don't have a start date
            NSDate *tomorrow = [midnight addTimeInterval:SECONDS_PER_DAY];
            [sql appendFormat:@" and ifnull(%@,0)<%i/*HideFuture*/",
             kFieldNameStartDate, (int)[tomorrow timeIntervalSinceReferenceDate]];
        }
        
        // Get future midnight date by adding the number of days we will show (as seconds). Again this will be in UTC
        NSDate* future = [midnight addTimeInterval:seconds];
        if (debug)
            XLog(@"LI:PITask: Cutoff days= %i; date = %@", (int)maxDays, future);
        
        //        // PI stores end times as local rather than UTC so we need to convert the local time to UTC
        //        // in order to compare with an end date. This will be a hack as srictly we need the number of
        //        // seconds offset on the given day to account for DST. That's the way that the display functions
        //        // work.
        //        NSInteger utcOffset = [[NSTimeZone systemTimeZone] secondsFromGMTForDate:midnight];
        //        if (debug)
        //            XLog(@"LI:PITask: UTC offset to local time = %i", utcOffset);
        
        // Only allow items with no duedate or a duedate before the threshold (not at as that would give 1 day too much)
        [sql appendFormat:@" and ifnull(%@,0)<%i/*MaxLimit-%i*/",
         kFieldNameDueDate, (int)[future timeIntervalSinceReferenceDate], (int)maxDays];
        
        // Only return tags in the show list if we have one
        NSString* notagSQL = nil;
        if ([prefs boolValue:kPIOptionsNoTagsFilter withDefault:kPIOptionsNoTagsFilterDefault])
            notagSQL = [NSString stringWithFormat:@"%@ is null/*SelectNoTags*/", kFieldNameTagID];

        NSString* tagSQL = nil;
        if (NSArray* tags = [prefs arrayValue:kPIOptionsTagsFilters withDefault:nil]) {
            NSMutableString* selectTags = [NSMutableString stringWithCapacity:tags.count * 3];
            for (NSString* tag in tags) {
                [selectTags appendString:tag withSeparator:@","];
            }
            if (selectTags.length > 0)
                tagSQL = [NSString stringWithFormat:@"%@ in (%@)/*SelectTags*/", kFieldNameTagID, selectTags];
        }

        // Add the tag SQL to the string
        if (notagSQL || tagSQL) {
            [sql appendString:@"and ("];
            if (tagSQL)
                [sql appendString:tagSQL];
            if (notagSQL) {
                if (tagSQL)
                    [sql appendString:@" or "];
                [sql appendString:notagSQL];
            }
            [sql appendString:@")"];
        }
        
        int showParents = [prefs intValue:kPIOptionsShowParent withDefault:kPIOptionsShowParentDefault];
        if (showParents == HideParentIfChildren)
            // Only allow items that don't have children
            [sql appendString:@" and T.ZCHILDCOUNTINTERNAL=0/*HideWithChild*/"];
        
        if ([prefs boolValue:kPIOptionsHideUndated withDefault:kPIOptionsHideUndatedDefault])
            // Only allow items that don't have a duedate
            [sql appendString:@" and T.ZENDDATEINTERNAL is not null/*HideUndated*/"];
        
        int actionType = [prefs intValue:kPIOptionsShowOnlyAction withDefault:kPIOptionsShowOnlyActionDefault];
        if (actionType >= 0)
            // Only allow items with the correct action
            [sql appendFormat:@" and T.ZACTIONINTEGER=%i/*OnlyAction*/", actionType];
        
        if ([prefs boolValue:kPIOptionsStarredOnly withDefault:kPIOptionsStarredOnlyDefault])
            // Only allow items that have been flagged
            [sql appendString:@" and T.ZFLAGGEDINTEGER=1/*StarredOnly*/"];
    }
    else
        [sql appendString:@" /*ShowAllTasks*/"];
    
    [sql appendString:@" group by T.Z_PK"];
    
    if (parentKey) {
        // Add sorting terms
        NSMutableString* sortExpr = [NSMutableString stringWithCapacity:200];
        
        [exec addSortField:sortExpr field:SortFieldOptionsReorder ascending:YES invert:NO prefs:prefs];
        [exec addSortField:sortExpr field:SortFieldOptionsDueDate ascending:YES invert:NO prefs:prefs];
        [sql appendString:@" order by "];
        [sql appendString:sortExpr];
    }
    else if (!key) {
        // Add sorting terms
        NSMutableString* sortExpr = [NSMutableString stringWithCapacity:200];
        
        int sort1 = [prefs intValue:kPIOptionsSortField1 withDefault:kPIOptionsSortField1Default];
        int sort2 = [prefs intValue:kPIOptionsSortField2 withDefault:kPIOptionsSortField2Default];
        
        // If we are using context filtering and neither of the sort settings is context then add a context sort
        if ((((PITaskStatus*)app.info).isAutoContextOn || ((PITaskStatus*)app.info).isFixedContextOn) && (sort1 != SortFieldOptionsContext) && (sort2 != SortFieldOptionsContext))
            [exec addSortField:sortExpr field:SortFieldOptionsContextFilter ascending:YES invert:NO prefs:prefs];
        
        BOOL sortAsc1 = [prefs boolValue:kPIOptionsSortField1Asc withDefault:kPIOptionsSortField1AscDefault];
        int noneLast1 = [prefs intValue:kPIOptionsSortField1Order withDefault:kPIOptionsSortField1OrderDefault];
        [exec addSortField:sortExpr field:sort1 ascending:sortAsc1 invert:(noneLast1 == SortFieldNoneOrderLast) prefs:prefs];
        
        BOOL sortAsc2 = [prefs boolValue:kPIOptionsSortField2Asc withDefault:kPIOptionsSortField2AscDefault];
        int noneLast2 = [prefs intValue:kPIOptionsSortField2Order withDefault:kPIOptionsSortField2OrderDefault];
        if (sort1 != sort2)
            [exec addSortField:sortExpr field:sort2 ascending:sortAsc2 invert:(noneLast2 == SortFieldNoneOrderLast) prefs:prefs];
        
        if (sortExpr.length > 0) {
            [sql appendString:@" order by "];
            [sql appendString:sortExpr];
        }
        
        // Restrict the number of items returned to the requested number		
        int queryLimit = search ? kMaxSearchItems : [prefs intValue:kPIOptionsMaxTasks withDefault:kPIOptionsMaxTasksDefault];
        [sql appendFormat:@" limit %i", queryLimit];
    }

    
    
//    NSArray* contexts = (NSArray*)[app getAttribute:kPIApplicationContextCurrent];
//    NSMutableString* contextList = [NSMutableString stringWithCapacity:100];
//    [contextList appendString:@"-1"];
//    if (contexts) {
//        for (PIContext* context in contexts) {
//            [contextList appendFormat:@",%i", context.key];
//        }
//    }
    
    
    NSArray* todos = FWDBGetRecordsFromConnection(sql, kSQLBasicTaskList, [NSTimeZone systemTimeZone], database, app, exec, plugin.isDebugMode);
    
    if (todos)
        return todos;
    else
        return [NSMutableArray array];
}

- (NSObject*)sqlBasicTaskList:(sqlite3_stmt*)sqlStatement application:(FWApplication *)application timeZone:(NSTimeZone*)timeZone
{
    // Read fields from SQL record
    NSString* text = FWDBReadColumnString(sqlStatement, 0);
    double cStart = sqlite3_column_double(sqlStatement, 1);
    BOOL hasStartTime = sqlite3_column_int(sqlStatement, 2);
    double cDue = sqlite3_column_double(sqlStatement, 3);
    BOOL allDay = sqlite3_column_int(sqlStatement, 4);
    NSInteger importance = sqlite3_column_int(sqlStatement, 5);
    NSString* priority = FWDBReadColumnString(sqlStatement, 6);
    NSInteger primaryKey = sqlite3_column_int(sqlStatement, 7);
    NSInteger flagged = sqlite3_column_int(sqlStatement, 8);
    NSInteger action = sqlite3_column_int(sqlStatement, 9);
    NSInteger cPercentComplete = sqlite3_column_int(sqlStatement, 10);
    NSString* icon = FWDBReadColumnString(sqlStatement, 11);
    NSInteger recurrance = sqlite3_column_int(sqlStatement, 12);
    NSString* project = FWDBReadColumnString(sqlStatement, 13);
    NSInteger parent = sqlite3_column_int(sqlStatement, 14);
    NSInteger child = sqlite3_column_int(sqlStatement, 15);

    NSString* calendarTitle = FWDBReadColumnString(sqlStatement, 16);
    NSString* calendarIcon = FWDBReadColumnString(sqlStatement, 17);
    UIColor* calendarColor = FWDBReadColumnBlobWithDefault(sqlStatement, 18, [UIColor clearColor]);
    NSTimeZone* calendarTZ = FWDBReadColumnBlobWithDefault(sqlStatement, 19, timeZone);
        
    NSInteger context = sqlite3_column_int(sqlStatement, 20);
    NSInteger tag = sqlite3_column_int(sqlStatement, 21);
    NSInteger children = sqlite3_column_int(sqlStatement, 22);
    
    NSInteger itemVersion = sqlite3_column_int(sqlStatement, 23);
    
    PITask* task = [PITask piTaskWithKey:[[NSNumber numberWithInt:primaryKey] stringValue]
                                 version:itemVersion * context
                                   title:text
                                    icon:icon
                               startDate:!hasStartTime ? FWLocalDateForUTC([NSDate nullableDateWithTimeIntervalSinceReferenceDate:cStart]) : [NSDate nullableDateWithTimeIntervalSinceReferenceDate:cStart]
                       startDateTimeZone:calendarTZ
                            startDateRaw:cStart
                            hasStartTime:hasStartTime
                                 dueDate:allDay ? FWLocalDateForUTC([NSDate nullableDateWithTimeIntervalSinceReferenceDate:cDue]) : [NSDate nullableDateWithTimeIntervalSinceReferenceDate:cDue]
                         dueDateTimeZone:calendarTZ
                              dueDateRaw:cDue
                                  allDay:allDay
                                calendar:calendarTitle
                            calendarIcon:calendarIcon
                           calendarColor:calendarColor
                               parentKey:[[NSNumber numberWithInt:child] stringValue]];
    
    task.contextClassification = (PIClassificationType)context;
    task.flagged = flagged;
    task.project = project;
    task.percentComplete = cPercentComplete;
    task.priority = priority;
    task.importance = (PIImportanceType)importance;
    task.action = (PIActionType)action;
    task.repeating = recurrance != 0;
    task.child = child != 0;
    task.parent = parent || (children > 0);
    
    return task;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Augmenting a task - returns a new task

+ (PITask*)augmentedBasicTask:(PITask*)task database:(FWDatabase*)database application:(FWApplication*)app
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return task;

    // Get a new copy of the task
    PITask* newTask = [[task copy] autorelease];
    newTask.augmented = YES;
    
    PITaskIntegration* exec = [PITaskIntegration implementationForApp:app];

    NSString* sql = [exec augmentedBasicTaskSQL:task application:app];

    NSArray* extras = FWDBGetRecordsFromConnection(sql, kSQLAugmentedTask, nil, database, app, exec, plugin.isDebugMode);
    
    extras = [extras objectAtIndex:0];
    newTask.notes = [extras objectAtIndex:0];
    if (newTask.notes.length == 0)
        newTask.notes = nil;

    newTask.percentComplete = [[extras objectAtIndex:1] intValue];
    newTask.context = [extras objectAtIndex:2];
    newTask.project = [extras objectAtIndex:3];
    newTask.sensitivity = (PISensitivityType)[[extras objectAtIndex:6] intValue];

    NSArray* tags = [extras objectAtIndex:5];
    if (tags.count > 0) {
        NSMutableArray* newTags = [NSMutableArray arrayWithCapacity:tags.count];
        NSArray* allTags = [PIIntegration arrayOfTags:app keys:nil database:database];
        for (NSString* key in tags) {
            if (PIContextObject* tag = [allTags piContextObjectForKey:key.intValue])
                [newTags addObject:tag];
        }
        if (newTags.count > 0) {
            [newTags sortUsingFunction:compareContextObjectNames context:nil];   
            newTask.tags = newTags;
        }
    }
    
    if (task.isRepeating) {
        NSArray* daysOfMonth = [extras objectAtIndex:7];
        NSMutableArray* daysOfWeek = [NSMutableArray arrayWithArray:[extras objectAtIndex:8]];
        NSInteger firstDayOfWeek = [[extras objectAtIndex:9] intValue];
        NSArray* monthsOfYear = [extras objectAtIndex:10];
        NSDate* endDate = [extras objectAtIndex:11];
        NSInteger occurances = [[extras objectAtIndex:12] intValue];
        NSInteger interval = [[extras objectAtIndex:13] intValue];
        NSInteger type = [[extras objectAtIndex:14] intValue];
        NSInteger taskType = [[extras objectAtIndex:15] intValue];
        
        newTask.recurrance = [PIIntegration recurrenceWithFrequency:type
                                                           interval:interval
                                                      daysOfTheWeek:daysOfWeek
                                                     daysOfTheMonth:daysOfMonth
                                                    monthsOfTheYear:monthsOfYear
                                                         occurances:occurances
                                                            endDate:endDate
                                                           fromDate:(PIRecurranceFromDateType)taskType];
        
#ifdef DEBUG
//            if (plugin.isDebugMode)
//                newTask.notes = [NSString stringWithFormat:
//                                 @"days of month = '%@'\n"
//                                 "days of week = '%@'\n"
//                                 "first day of week = %d\n"
//                                 "months of year = '%@'\n"
//                                 "end date = '%@'\n"
//                                 "occurances = '%d'\n"
//                                 "interval = '%d'\n"
//                                 "type = '%d'\n"
//                                 "taskType = '%d'\n"
//                                 "------------------------\n"
//                                 "%@",
//                                 daysOfMonth,
//                                 daysOfWeek,
//                                 firstDayOfWeek,
//                                 monthsOfYear,
//                                 endDate,
//                                 occurances,
//                                 interval,
//                                 type,
//                                 taskType,
//                                 newTask.notes];
#endif
    }

    NSString* locTitle = [extras objectAtIndex:16];
    PILocation* locn = [PILocation piLocationWithTitle:locTitle];
    
    NSString* locDesc = [extras objectAtIndex:17];
    if (locDesc.length > 0)
        locn.description = locDesc;
    
    double locLat = [[extras objectAtIndex:18] doubleValue];
    double locLong = [[extras objectAtIndex:19] doubleValue];
    if (locLong != MAXFLOAT) {
        CLLocation* coords = [[[CLLocation alloc] initWithLatitude:locLat longitude:locLong] autorelease];
        locn.coordinates = coords;
    }
    
    if ((locn.title.length > 0) || locn.coordinates)
        [newTask addLocation:locn];
    
    return newTask;
}

- (NSNumber*)sqlAugmentedBasicTask:(sqlite3_stmt*)sqlStatement application:application
{
    // Read fields from SQL record
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSString* notes = FWDBReadColumnString(sqlStatement, 1);
    double percentage = sqlite3_column_double(sqlStatement, 2);
    NSString* context = FWDBReadColumnString(sqlStatement, 3);
    NSString* project = FWDBReadColumnString(sqlStatement, 4);
    NSInteger childCount = sqlite3_column_int(sqlStatement, 5);
    NSString* tags = FWDBReadColumnString(sqlStatement, 6);
    
    NSArray* daysOfMonth = FWDBReadColumnBlobToArray(sqlStatement, 7);
    NSArray* daysOfWeek = FWDBReadColumnBlobToArray(sqlStatement, 8);
    double endDate = sqlite3_column_double(sqlStatement, 9);
    NSInteger cFirstDayOfWeek = sqlite3_column_int(sqlStatement, 10);
    NSArray* monthsOfYear = FWDBReadColumnBlobToArray(sqlStatement, 11);
    NSInteger occurances = sqlite3_column_int(sqlStatement, 12);
    NSInteger interval = sqlite3_column_int(sqlStatement, 13);
    NSInteger type = sqlite3_column_int(sqlStatement, 14);
    NSInteger taskType = sqlite3_column_int(sqlStatement, 15);
    NSInteger sensitivity = sqlite3_column_int(sqlStatement, 16);

    NSString* locTitle = FWDBReadColumnString(sqlStatement, 17);
    NSString* locDescription = FWDBReadColumnString(sqlStatement, 18);
    double locLat = FWDBReadColumnDoubleWithDefault(sqlStatement, 19, MAXFLOAT);
    double locLong = FWDBReadColumnDoubleWithDefault(sqlStatement, 20, MAXFLOAT);

    return [NSArray arrayWithObjects:
            notes,
            [NSNumber numberWithInt:percentage],
            context,
            project,
            [NSNumber numberWithInt:childCount],
            [tags componentsSeparatedByString:@","],
            [NSNumber numberWithInt:sensitivity],
            daysOfMonth,
            daysOfWeek,
            [NSNumber numberWithInt:cFirstDayOfWeek],
            monthsOfYear,
            [NSDate dateWithTimeIntervalSinceReferenceDate:endDate],
            [NSNumber numberWithInt:occurances],
            [NSNumber numberWithInt:interval],
            [NSNumber numberWithInt:type],
            [NSNumber numberWithInt:taskType],
            locTitle,
            locDescription,
            [NSNumber numberWithDouble:locLat],
            [NSNumber numberWithDouble:locLong],
            nil];
}


+ (void)addAlarmDataToTaskList:(FWApplication*)app database:(FWDatabase*)database tasks:(NSArray*)tasks prefs:(FWPreferences*)prefs
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return;
    
    
    // Get a list of keys
    NSMutableString* keys = [NSMutableString stringWithCapacity:50];
    NSMutableArray* lookups = [NSMutableArray arrayWithCapacity:tasks.count];
    for (PITask* task in tasks) {
        if (!task.isNative) {
            [keys appendString:task.key withSeparator:@","];
            
            // Use short list of items for lookup later
            [lookups addObject:task];
        }
    }
    
    if (keys.length > 0) {
        PITaskIntegration* exec = [PITaskIntegration implementationForApp:app];

        if (NSString* alarmsSql = [exec addAlarmDataToTaskListSQL:app keys:keys prefs:prefs]) {
            // Get a list of alarms for the events in ascending date order
            NSArray* alarms = FWDBGetSortedRecordsFromConnection(alarmsSql, kSQLAlarms, kFieldNameStartDate, database, app, exec, compareAlarmTimes, plugin.isDebugMode);
            
            for (PIAlarm* alarm in alarms) {
                if (PITask* task = [lookups piObjectForKey:alarm.key]) {
                    if (!task.alarms) {
                        task.alarms = [NSMutableArray arrayWithObject:alarm];
                    }
                    else {
                        [(NSMutableArray*)task.alarms addObject:alarm];
                    }
                }
            }
        }
    }
}

- (NSObject*)sqlAlarms:(sqlite3_stmt*)sqlStatement context:(NSObject*)context
{
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSInteger key = sqlite3_column_int(sqlStatement, 1);
    NSInteger type = sqlite3_column_int(sqlStatement, 6);

    if (type == 0) {
        NSInteger relative = sqlite3_column_int(sqlStatement, 2);
        NSInteger absolute = sqlite3_column_int(sqlStatement, 3);
        
        if (!context || (context == kFieldNameStartDate)) {
            double start = sqlite3_column_double(sqlStatement, 4);
            NSInteger allDay = sqlite3_column_int(sqlStatement, 5);
            
//            if (allDay)
//                context = FWLocalDateForUTC([NSDate nullableDateWithTimeIntervalSinceReferenceDate:start]);
//            else
                context = [NSDate nullableDateWithTimeIntervalSinceReferenceDate:start];
        }
        
        
        if (relative != 0)
            return [PIAlarm alarmWithKey:[[NSNumber numberWithInt:key] stringValue]
                            relativeDate:-relative
                                baseDate:(NSDate*)context
                                    type:kPIEventAlarmTypeNative
                               fixedTime:YES];
        else
            return [PIAlarm alarmWithKey:[[NSNumber numberWithInt:key] stringValue]
                                    date:[NSDate nullableDateWithTimeIntervalSinceReferenceDate:absolute]
                                    type:kPIEventAlarmTypeNative];
    }
    else {
        double latitude = sqlite3_column_double(sqlStatement, 7);
        double longitude = sqlite3_column_double(sqlStatement, 8);
        NSString* title = FWDBReadColumnString(sqlStatement, 9);
        NSString* description = FWDBReadColumnString(sqlStatement, 10);
        
        CLLocation* location = [[[CLLocation alloc] initWithLatitude:latitude longitude:longitude] autorelease];
        return [PIAlarm alarmWithKey:[[NSNumber numberWithInt:key] stringValue]
                                type:kPIEventAlarmTypeLocationArrive location:title coordinates:location];
    }
}


// ******************************************************************************************
#pragma mark -
#pragma mark Fetching a list of contexts

+ (NSInteger)numberOfLocationContexts:(FWApplication *)application
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)application.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return 0;
    
    PITaskIntegration* exec = [PITaskIntegration implementationForApp:application];
    NSString* sql = [exec numberOfLocationContextsSQL:application];
    NSArray* items = FWDBGetRecords(sql, kSQLLocationContexts, nil, application.databasePath, application, exec, plugin.isDebugMode);
    
    if (items)
        return [[items objectAtIndex:0] intValue];
    else
        return 0;
}

- (NSObject*)sqlNumberOfLocationContexts:(sqlite3_stmt*)sqlStatement application:(FWApplication *)application
{
    NSInteger number = sqlite3_column_int(sqlStatement, 0);
    
    return [NSNumber numberWithInt:number];
}

    
// ******************************************************************************************
#pragma mark -
#pragma mark Fetching a list of contexts

+ (NSArray*)arrayOfContexts:(FWApplication *)application earliest:(NSDate*)earliest counts:(BOOL)counts
{  
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)application.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return [NSArray array];
    
    
    PITaskIntegration* exec = [PITaskIntegration implementationForApp:application];
    NSString* sql = [exec arrayOfContextsSQL:application earliest:earliest counts:counts];
    NSArray* items = FWDBGetRecords(sql, kSQLArrayOfContexts, nil, application.databasePath, application, exec, plugin.isDebugMode);

    if (items)
        return items;
    else
        return [NSArray array];
}

- (NSObject*)sqlArrayOfContexts:(sqlite3_stmt*)sqlStatement application:(FWApplication *)application
{
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSInteger key = sqlite3_column_int(sqlStatement, 1);
    NSString* title = FWDBReadColumnString(sqlStatement, 2);
    NSString* icon = FWDBReadColumnString(sqlStatement, 3);
    double radius = sqlite3_column_double(sqlStatement, 4);
    NSInteger count = sqlite3_column_int(sqlStatement, 5);
    
    if ([icon length] > 0)
        title = [title stringByAppendingFormat:@" %@", icon];
    
    PIContext* context = [PIContext contextForKey:key title:title];
    context.count = count;
    context.locationAware = radius > 0;

    return context;
}

+ (NSURL*)urlForShowTask:(PITask*)task application:(FWApplication*)app
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return nil;

    // We need PI 2.5 or better for direct access
    if (plugin.dbVersion < PIDatabaseVersion250)
        return nil;
    
    // Get the highest current task id
    PITaskIntegration* exec = [PITaskIntegration implementationForApp:app];
    NSString* sql = [exec infoForNewTaskSQL:app];
    
    NSArray* results = FWDBGetRecords(sql, kSQLMaxTaskID, nil, app.databasePath, app, exec, plugin.isDebugMode);
    NSArray* info = [results objectAtIndex:0];
    NSString* uuid = [info objectAtIndex:1];
    
    return [NSURL URLWithString:[NSString stringWithFormat:@"informant://displayItem?x-coredata://%@/%@/p%@",
                                 uuid,
                                 kPIEntityTypeTask,
                                 task.key]];
}    
    

// ******************************************************************************************
#pragma mark -
#pragma mark URL to create a new task

+ (NSURL*)urlForNewTask:(NSString*)title importance:(enum PIImportanceType)importance application:(FWApplication*)app
{
    // If PI not found then do nothing
    PIPlugin* plugin = (PIPlugin*)app.fwPlugin;
    if (!plugin.isPocketInformantPresent)
        return nil;
    
    PITaskIntegration* exec = [PITaskIntegration implementationForApp:app];

    NSString* sql = [exec infoForNewTaskSQL:app];

    NSArray* results = FWDBGetRecords(sql, kSQLMaxTaskID, nil, app.databasePath, app, exec, plugin.isDebugMode);
    NSArray* info = [results objectAtIndex:0];
    
    return [exec urlForNewTask:title importance:importance application:app info:info];
}

- (NSObject*)sqlMaxTaskID:(sqlite3_stmt*)sqlStatement application:(FWApplication *)application
{
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSInteger key = sqlite3_column_int(sqlStatement, 1);
    NSString* uuid = version == 1 ? @"" : FWDBReadColumnString(sqlStatement, 2);
    NSString* inbox = version == 1 ? @"Inbox" : FWDBReadColumnString(sqlStatement, 3);
    NSString* calendar = version == 1 ? @"Internal Task Calendar" : FWDBReadColumnString(sqlStatement, 4);
    
    return [NSArray arrayWithObjects:
            [NSNumber numberWithInt:key],
            uuid,
            inbox,
            calendar,
            nil];
}


- (NSObject*)sqlNotesForTask:(sqlite3_stmt *)sqlStatement application:(FWApplication *)application
{
    NSInteger version = sqlite3_column_int(sqlStatement, 0);
    NSString* notes = FWDBReadColumnString(sqlStatement, 1);
    
    return notes;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Completing a task

+ (BOOL)completeTask:(PITask*)task application:(FWApplication*)app
{
    FWDatabase* database = nil;
    @try {
        XLog(@"LI:%@: Attempting to complete PI task; task = %@", app.resourceName, task);
        
        database = FWDBConnectDatabase(app.databasePath, app, SQLITE_OPEN_READWRITE);
        if (database) {
            PITaskIntegration* exec = [PITaskIntegration implementationForApp:app];
            NSString* completeSql = [exec completeTaskSQL:task application:app];
            
            FWDBUpdate(completeSql, database, app, app.fwPlugin.isDebugMode);
            XLog(@"LI:%@: Completed DB task", app.resourceName);

            NSString* infoSql = [exec infoForNewTaskSQL:app];
            NSArray* results = FWDBGetRecordsFromConnection(infoSql, kSQLMaxTaskID, nil, database, app, exec, NO);
            NSArray* info = [results objectAtIndex:0];
            NSString* uuid = [info objectAtIndex:1];
            
            NSURL* taskUrl = [NSURL URLWithString:[NSString stringWithFormat:@"x-coredata://%@/%@/p%@",
                                                   uuid,
                                                   kPIEntityTypeTask,
                                                   task.key]];
            
            UIPasteboard* pasteBoard = [UIPasteboard pasteboardWithName:kCompletePasteboard create:YES];
            pasteBoard.persistent = YES;

            XLog(@"LI:%@: Accessed complete pasteboard; board = %@", app.resourceName, pasteBoard);

            NSMutableArray* pasteBoardSet = [NSMutableArray arrayWithCapacity:pasteBoard.items.count];
            for (NSURL* item in pasteBoard.URLs)
                [pasteBoardSet addObject:[NSDictionary dictionaryWithObjectsAndKeys:item, @"public.url", nil]];
            [pasteBoardSet addObject:[NSDictionary dictionaryWithObjectsAndKeys:taskUrl, @"public.url", nil]];
            pasteBoard.items = pasteBoardSet;
            
            XLog(@"LI:%@: Added task to complete set; tasks = %@; items = %@", app.resourceName, pasteBoardSet, pasteBoard.items);

#ifdef DEBUG

            UIPasteboard* testPasteBoard = [UIPasteboard pasteboardWithName:kCompletePasteboard create:NO];
            XLog(@"LI:%@: Enumerating current complete pasteboard; items = %@", app.resourceName, testPasteBoard.items);

            if (testPasteBoard && [testPasteBoard.URLs count])
            {
                [testPasteBoard.URLs enumerateObjectsUsingBlock:^(NSURL* objectURI, NSUInteger idx, BOOL *stop) {
                    XLog(@"LI:%@: Clipboard item; %@", app.resourceName, objectURI);
                }];
            }
            
//            [UIPasteboard removePasteboardWithName:kCompletePasteboard];
#endif
            return YES;
        }
    }
    @finally {
        FWDBDisconnectDatabase(database);
    }

    return NO;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Database support

- (NSObject*)objectForSQLRecord:(sqlite3_stmt *)sqlStatement withId:(NSInteger)sqlid context:(NSObject*)context application:(FWApplication *)application
{
    if (sqlid == kSQLBasicTaskList)
        return [self sqlBasicTaskList:sqlStatement application:application timeZone:(NSTimeZone*)context];
    else if (sqlid == kSQLAugmentedTask)
        return [self sqlAugmentedBasicTask:sqlStatement application:application];
    else if (sqlid == kSQLArrayOfContexts)
        return [self sqlArrayOfContexts:sqlStatement application:application];
    else if (sqlid == kSQLMaxTaskID)
        return [self sqlMaxTaskID:sqlStatement application:application];
    else if (sqlid == kSQLNotes)
        return [self sqlNotesForTask:sqlStatement application:application];
    else if (sqlid == kSQLLocationContexts)
        return [self sqlNumberOfLocationContexts:sqlStatement application:application];
    else if (sqlid == kSQLAlarms)
        return [self sqlAlarms:sqlStatement context:context];
    else
        return nil;
}

@end
